package hydrap2p.library;

import hydrap2p.helper.Utility;
import hydrap2p.logging.Level;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.TreeMap;
import java.util.Map.Entry;

/**
 * Directly accesses the database, and handles loading/saving ItemInfos into it
 * @author Walter Huf
 *
 */
public class Database {
	private volatile static Database instance=null;
	private Connection conn;
	
	private Database() throws SQLException {
		this("jdbc:hsqldb:mem:test", "SA","");
	}
	private Database(String jdbcconnection, String username, String password) throws SQLException {
		String path=Utility.appendPath(Utility.getUserHome(),"hydrap2p");
		path=Utility.appendPath(path, "library.db");

//        conn=DriverManager.getConnection("jdbc:hsqldb:file:"+path, "SA","");
        conn=DriverManager.getConnection(jdbcconnection, username, password);
		conn.setAutoCommit(false);
	}
	public static Database getInstance() throws SQLException {
		if (instance == null)
		{
			synchronized(Database.class)
			{
				if (instance == null)
					instance=new Database();
			}
		}
		return instance;
	}
	public static Database getInstance(String jdbcconnection, String username, String password) throws SQLException {
		if (instance == null)
		{
			synchronized(Database.class)
			{
				if (instance == null)
					instance=new Database(jdbcconnection, username, password);
			}
		}
		return instance;
	}
	
	public boolean isInited()
	{
		Statement s;
		try {
			s = conn.createStatement();
		} catch (SQLException e) {
			return false;
		}
		try {
			s.execute("SELECT count(uid) FROM Objects");
		} catch (SQLException e) {
			try {
				s.close();
			} catch (SQLException e1) {
			}
			return false;
		}

		try {
			ResultSet set=s.getResultSet();
			return true;
		} catch (SQLException e) {
			return false;
		}
	}

	/**
	 * Create a blank database
	 * @throws SQLException
	 */
	public void initDB() throws SQLException {
		Statement s=conn.createStatement();
		
		// Stores simple information about the objects
		s.execute("CREATE TABLE Objects (uid BIGINT GENERATED BY DEFAULT AS IDENTITY(START WITH 1) PRIMARY KEY," +
					"name VARCHAR(255)," +
					"path VARCHAR(65535)," +
					"size BIGINT," +
					"dir BOOLEAN," +
					"shared BOOLEAN," +
					"lastscan DATETIME" +
					")");
		// stores what hash generators have parsed each object
		s.execute("CREATE TABLE Hashers (uid BIGINT, " +
					"hasher VARCHAR(255)," +
					"CONSTRAINT pk_Hashers PRIMARY KEY (uid,hasher)" +
					")");
		// Stores hashes about the objects
		s.execute("CREATE TABLE Hashes (uid BIGINT," +
					"algorithm VARCHAR(255)," +
					"offset BIGINT," +
					"len BIGINT,"+
					"verified BOOLEAN," +
					"downloaded BOOLEAN," +
					"data VARCHAR(255)," +
					"CONSTRAINT pk_Hashes PRIMARY KEY (uid,algorithm,offset,len)" +
					")");
		// Stores target hashes about what the objects should be
		s.execute("CREATE TABLE TargetHashes (uid BIGINT," +
					"algorithm VARCHAR(255)," +
					"offset BIGINT," +
					"len BIGINT,"+
					"verified BOOLEAN," +
					"downloaded BOOLEAN," +
					"data VARCHAR(255)," +
					"CONSTRAINT pk_TargetHashes PRIMARY KEY (uid,algorithm,offset,len)" +
					")");
		// Stores any extra information
		s.execute("CREATE TABLE Extras (uid BIGINT, " +
					"key VARCHAR(255)," +
					"value VARCHAR(65535)," +
					"CONSTRAINT pk_Extras PRIMARY KEY (uid,key)" +
					")");
		// Stores normal metadata
		s.execute("CREATE TABLE Metadatas (uid BIGINT, " +
					"key VARCHAR(255)," +
					"value VARCHAR(65535)," +
					"CONSTRAINT pk_Metadatas PRIMARY KEY (uid,key)" +
					")");
		// stores what metadata parsers have parsed each object
		s.execute("CREATE TABLE Children (uid BIGINT, " +
					"child BIGINT," +
					"index INT," +
					"CONSTRAINT pk_Children PRIMARY KEY (uid,child)" +
					")");
		// stores what downloaded ranges a file has
		s.execute("CREATE TABLE DownloadedRanges (uid BIGINT, " +
					"offset BIGINT, len BIGINT," +
					"CONSTRAINT pk_DownloadedRanges PRIMARY KEY (uid,offset,len)" +
					")");
		// stores what verified ranges a file has
		s.execute("CREATE TABLE VerifiedRanges (uid BIGINT, " +
					"offset BIGINT, len BIGINT," +
					"CONSTRAINT pk_VerifiedRanges PRIMARY KEY (uid,offset,len)" +
					")");
		

		// stores what shared folders we know about
		s.execute("CREATE TABLE SharedFolders (uid BIGINT PRIMARY KEY)");

		s.close();
		conn.commit();
		
		log(Level.INFO,"Library - Initialize DB", "Initialized database");
	}
	public void clearDB(){
		Statement s=null;
		try {
			s=conn.createStatement();
		} catch (SQLException e) {
			// Failed to connect to database, don't try to clear it
			return;
		}
		try {
			s.execute("DROP TABLE Objects");
			s.execute("DROP TABLE Hashes");
			s.execute("DROP TABLE Hashers");
			s.execute("DROP TABLE TargetHashes");
			s.execute("DROP TABLE Extras");
			s.execute("DROP TABLE Metadatas");
			s.execute("DROP TABLE Children");
			s.execute("DROP TABLE DownloadedRanges");
			s.execute("DROP TABLE VerifiedRanges");

			s.execute("DROP TABLE SharedFolders");

			conn.commit();
			s.close();

		} catch (SQLException e) {
			e.printStackTrace();
			return;
		}
		
	}
	public void close()
	{
		try {
			conn.close();
		} catch (SQLException e) {
		}
		shutdown();
	}
	
	public static void shutdown()
	{
		synchronized(instance)
		{
			instance=null;
		}
	}
	
	private static void log(Level level, String source, String reason)
	{
		LibraryLogging.log(level, source, reason);
	}
	
	/**
	 * Returns a blank new ItemInfo, with a globally unique UID
	 * @return A new ItemInfo object
	 */
	public ItemInfo create()
	{
		Statement s;
		try {
			s = conn.createStatement();
			s.executeUpdate("INSERT INTO Objects (name,path,dir,shared) VALUES ('','',0,0)",Statement.RETURN_GENERATED_KEYS);
		
			ResultSet a=s.getGeneratedKeys();
			long uid=-1;
			a.next();
			uid=a.getLong(1);
			s.close();
			conn.commit();
			ItemInfo temp=new ItemInfo();
			temp.uid=uid;
			temp.metadata=new TreeMap<String,String>();
			temp.extra=new TreeMap<String,String>();
			temp.children=new LinkedList<Long>();
			temp.targetHashesEx=new LinkedList<HashDataEx>();
			temp.finishedHashesEx=new LinkedList<HashDataEx>();
			temp.downconvertHashData();
			return temp;
		} catch (SQLException e) {
			log(Level.FATAL, "Library - Create", "Failed to create new Shared Item in the database");
			log(Level.DEBUG, "Library - Create", e.getMessage());
			e.printStackTrace();
			return null;
		}
	}
	
	public void delete(long uid)
	{
		Statement s;
		try {
			s = conn.createStatement();
		} catch (SQLException e) {
			log(Level.FATAL, "Library - Delete", "Failed to connect to database");
			log(Level.DEBUG, "Library - Delete", e.getMessage());
			e.printStackTrace();
			return;
		}
		try {
			s.execute("Delete FROM Objects WHERE uid="+uid);
			s.execute("Delete FROM Hashers WHERE uid="+uid);
			s.execute("Delete FROM Hashes WHERE uid="+uid);
			s.execute("Delete FROM TargetHashes WHERE uid="+uid);
			s.execute("Delete FROM Extras WHERE uid="+uid);
			s.execute("Delete FROM Metadatas WHERE uid="+uid);
			s.execute("Delete FROM Children WHERE uid="+uid);
			s.execute("Delete FROM DownloadedRanges WHERE uid="+uid);
			s.execute("Delete FROM VerifiedRanges WHERE uid="+uid);
			conn.commit();
			s.close();
		} catch (SQLException e) {
			log(Level.FATAL, "Library - Delete", "Failed to delete Shared Item from the database due to improper sql");
			log(Level.DEBUG, "Library - Delete", e.getMessage());
			e.printStackTrace();
			return;
		}
		finally
		{
		}
		return;
	}
	
	private List<ItemInfo> search(String where)
	{

		List<ItemInfo> ret=new LinkedList<ItemInfo>();

		Statement s;
		try {
			s = conn.createStatement();
		} catch (SQLException e) {
			log(Level.FATAL, "Library - Delete", "Failed to connect to database");
			log(Level.DEBUG, "Library - Delete", e.getMessage());
			e.printStackTrace();
			return null;
		}
		try {
			s.execute("SELECT uid,name,path,dir,shared FROM Objects WHERE "+where);
		} catch (SQLException e) {
			log(Level.FATAL, "Library - Search Filename", "Failed to search the database due to improper sql");
			log(Level.DEBUG, "Library - Search Filename", e.getMessage());
			e.printStackTrace();
			return null;
		}
		try {
			ResultSet set=s.getResultSet();
			while (set.next())	// could not find any items
			{
				ret.add(load(set.getLong(1)));
			}
			s.close();
		} catch (SQLException e) {
			log(Level.FATAL, "Library - Search Filename", "Failed to search the database, failed to parse");
			log(Level.DEBUG, "Library - Search Filename", e.getMessage());
			e.printStackTrace();
			return null;
		}
		return ret;
	}
	public List<ItemInfo> searchPathFilename(String path,String file) 
	{
		if (path!=null && file!=null)
			return search("path like '%"+path+"%' and name like '%"+file+"%'");
		else if (path==null && file!=null)
			return search("name like '%"+file+"%'");
		else if (path!=null && file==null)
			return search("path like '%"+path+"%'");
		else
			return new LinkedList<ItemInfo>();
	}
	public List<ItemInfo> searchExactPathFilename(String path, String file) 
	{
		List<ItemInfo> ret;
		if (path!=null && file!=null)
			ret= search("path like '"+path+"' and name like '"+file+"'");
		else if (path==null && file!=null)
			ret= search("name like '"+file+"'");
		else if (path!=null && file==null)
			ret= search("path like '"+path+"'");
		else
			ret=new LinkedList<ItemInfo>();
		if (ret.size()>1)
			log(Level.MISHAP,"Library - Search Exact PathFilename", "Searched for path("+path+")file("+file+") and found "+ret.size()+" results");
		return ret;
	}
	
	public List<ItemInfo> searchHash(String algorithm, String hashdata)
	{

		List<ItemInfo> ret=new LinkedList<ItemInfo>();

		PreparedStatement s;
		try {
			s=conn.prepareStatement("SELECT uid FROM Hashes WHERE algorithm=? and data=?");
			s.setString(1, algorithm);
			s.setString(2, hashdata);
			s.execute();
		} catch (SQLException e) {
			log(Level.FATAL, "Library - Search Filename", "Failed to search the database due to improper sql");
			log(Level.DEBUG, "Library - Search Filename", e.getMessage());
			e.printStackTrace();
			return null;
		}
		try {
			ResultSet set=s.getResultSet();
			while (set.next())	// could not find any items
			{
				ret.add(load(set.getLong(1)));
			}
			s.close();
		} catch (SQLException e) {
			log(Level.FATAL, "Library - Search Filename", "Failed to search the database, failed to parse");
			log(Level.DEBUG, "Library - Search Filename", e.getMessage());
			e.printStackTrace();
			return null;
		}
		return ret;
	}
	
	public List<Long> searchDownloadedUnhashed(String algorithm)
	{
		List<Long> ret=new LinkedList<Long>();

		PreparedStatement s;
		try {
			s=conn.prepareStatement("SELECT DISTINCT uid FROM TargetHashes WHERE algorithm=? AND downloaded=? AND verified=?");
			s.setString(1, algorithm);
			s.setBoolean(2, true);
			s.setBoolean(3, false);
			s.execute();
		} catch (SQLException e) {
			log(Level.FATAL, "Library - Search Downloaded/Unhashed", "Failed to search the database due to improper sql");
			log(Level.DEBUG, "Library - Search Downloaded/Unhashed", e.getMessage());
			e.printStackTrace();
			return null;
		}
		try {
			ResultSet set=s.getResultSet();
			while (set.next())	// could not find any items
			{
				ret.add(set.getLong(1));
			}
			s.close();
		} catch (SQLException e) {
			log(Level.FATAL, "Library - Search Downloaded/Unhashed", "Failed to search the database, failed to parse");
			log(Level.DEBUG, "Library - Search Downloaded/Unhashed", e.getMessage());
			e.printStackTrace();
			return null;
		}
		return ret;
	}
	
	public long size() throws SQLException
	{
		Statement s;
		try {
			s = conn.createStatement();
		} catch (SQLException e) {
			log(Level.FATAL, "Library - Count", "Failed to connect to database");
			log(Level.DEBUG, "Library - Count", e.getMessage());
			throw e;
		}
		try {
			s.execute("SELECT count(uid) FROM Objects");
		} catch (SQLException e) {
			s.close();
			log(Level.FATAL, "Library - Count", "Failed to count Shared Items in the database due to improper sql");
			log(Level.DEBUG, "Library - Count", e.getMessage());
			throw e;
		}

		try {
			ResultSet set=s.getResultSet();
			if (!set.next())	// could not find any items
				return -2;
			s.close();
			return set.getLong(1);
		
		} catch (SQLException e) {
			log(Level.FATAL, "Library - Count", "Could not count Shared Items in the database, could not parse results");
			log(Level.DEBUG, "Library - Count", e.getMessage());
			throw e;
		}
	}
	
	public List<SharedItemInfo> getAll() 
	{

		Statement s;
		try {
			s = conn.createStatement();
		} catch (SQLException e) {
			log(Level.FATAL, "Library - GetAll", "Failed to connect to database");
			log(Level.DEBUG, "Library - GetAll", e.getMessage());
			return null;
		}
		try {
			s.execute("SELECT uid FROM Objects");
		} catch (SQLException e) {
			log(Level.FATAL, "Library - GetAll", "Failed to count Shared Items in the database due to improper sql");
			log(Level.DEBUG, "Library - GetAll", e.getMessage());
			return null;
		}

		try {
			ResultSet set=s.getResultSet();
			s.close();
			return new SqlListSharedItemInfo(set);
		
		} catch (SQLException e) {
			log(Level.FATAL, "Library - GetAll", "Could not count Shared Items in the database, could not parse results");
			log(Level.DEBUG, "Library - GetAll", e.getMessage());
			return null;
		}
	}
	/**
	 * Get the downloaded ranges for this uid
	 * Will return an empty list if the uid is not found, or if there are no downloaded ranges
	 * @param uid
	 * @return
	 */
	public List<Range> getDownloadedRanges(long uid) {
		return loadRangeList("DownloadedRanges",uid);
	}
	/**
	 * Get the verified ranges for this uid
	 * Will return an empty list if the uid is not found, or if there are no verified ranges
	 * @param uid
	 * @return
	 */
	public List<Range> getVerifiedRanges(long uid){
		return loadRangeList("VerifiedRanges",uid);
	}
	
	/**
	 * Get the downloaded ranges for this uid
	 * Will return an empty list if the uid is not found, or if there are no downloaded ranges
	 * @param uid
	 * @return
	 */
	public void setDownloadedRanges(long uid, List<Range> ranges) {
		saveRangeList("DownloadedRanges",uid, ranges);
	}
	/**
	 * Get the verified ranges for this uid
	 * Will return an empty list if the uid is not found, or if there are no verified ranges
	 * @param uid
	 * @return
	 */
	public void setVerifiedRanges(long uid, List<Range> ranges) {
		saveRangeList("VerifiedRanges",uid,ranges);
	}
	
	/**
	 * Loads a list of all of the root shared folders 
	 * @return
	 */
	public List<ItemInfo> loadSharedFolderList()
	{
		List<ItemInfo> ret=new LinkedList<ItemInfo>();
		Statement s;
		try {
			s = conn.createStatement();
		} catch (SQLException e) {
			log(Level.FATAL, "Library - Load Shared Folder List", "Failed to connect to database");
			log(Level.DEBUG, "Library - Load Shared Folder List", e.getMessage());
			e.printStackTrace();
			return null;
		}
		try {
			s.execute("SELECT uid FROM SharedFolders");
		} catch (SQLException e) {
			log(Level.FATAL, "Library - Load Shared Folder List", "Failed to load list from the database due to improper sql");
			log(Level.DEBUG, "Library - Load Shared Folder List", e.getMessage());
			e.printStackTrace();
			return null;
		}
		try {
			ResultSet set=s.getResultSet();
			while (set.next())
			{
				ret.add(load(set.getLong(1)));
			}
			set.close();
			s.close();
		} catch (SQLException e) {
			log(Level.FATAL, "Library - Load Shared Folder List", "Could not load list from the database, could not parse results");
			log(Level.DEBUG, "Library - Load Shared Folder List", e.getMessage());
			e.printStackTrace();
			return null;
		}
		return ret;
	}
	
	/**
	 * Adds this ItemInfo as a root shared folder 
	 * @return
	 */
	public void newSharedFolder(ItemInfo toadd)
	{
		Statement s;
		try {
			s = conn.createStatement();
		} catch (SQLException e) {
			log(Level.FATAL, "Library - Add Shared Folder", "Failed to connect to database");
			log(Level.DEBUG, "Library - Add Shared Folder", e.getMessage());
			e.printStackTrace();
			return;
		}
		try {
			s.execute("INSERT INTO SharedFolders (uid) VALUES ("+toadd.uid+")");
			s.close();
			conn.commit();
		} catch (SQLException e) {
			log(Level.FATAL, "Library - Add Shared Folder", "Failed to load list from the database due to improper sql");
			log(Level.DEBUG, "Library - Add Shared Folder", e.getMessage());
			e.printStackTrace();
			return;
		}
		return;
	}
	/**
	 * Removes this ItemInfo as a root shared folder 
	 * @return
	 */
	public void removeSharedFolder(ItemInfo toadd)
	{
		Statement s;
		try {
			s = conn.createStatement();
		} catch (SQLException e) {
			log(Level.FATAL, "Library - Remove Shared Folder", "Failed to connect to database");
			log(Level.DEBUG, "Library - Remove Shared Folder", e.getMessage());
			e.printStackTrace();
			return;
		}
		try {
			s.execute("DELETE FROM SharedFolders WHERE uid="+toadd.uid);
			s.close();
			conn.commit();
		} catch (SQLException e) {
			log(Level.FATAL, "Library - Remove Shared Folder", "Failed to load list from the database due to improper sql");
			log(Level.DEBUG, "Library - Remove Shared Folder", e.getMessage());
			e.printStackTrace();
			return;
		}
		return;
	}

	public ItemInfo load(long uid)
	{
		ItemInfo ret=new ItemInfo();
		Statement s;
		try {
			s = conn.createStatement();
		} catch (SQLException e) {
			log(Level.FATAL, "Library - Load", "Failed to connect to database");
			log(Level.DEBUG, "Library - Load", e.getMessage());
			e.printStackTrace();
			return null;
		}
		try {
			s.execute("SELECT uid,name,path,size,dir,shared,lastscan FROM Objects WHERE uid="+uid);
		} catch (SQLException e) {
			log(Level.FATAL, "Library - Load", "Failed to load Shared Item from the database due to improper sql");
			log(Level.DEBUG, "Library - Load", e.getMessage());
			e.printStackTrace();
			return null;
		}
		try {
			ResultSet set=s.getResultSet();
			if (!set.next())	// could not find any items
				return null;
			ret.uid=set.getLong(1);
			ret.name=set.getString(2);
			ret.pathname=set.getString(3);
			ret.size=set.getLong(4);
			ret.isDirectory=set.getBoolean(5);
			ret.isShared=set.getBoolean(6);
			ret.lastscan=set.getTimestamp(7);
			set.close();
			s.close();
			conn.commit();
		} catch (SQLException e) {
			log(Level.FATAL, "Library - Load", "Could not load Shared Item from the database, could not parse results");
			log(Level.DEBUG, "Library - Load", e.getMessage());
			e.printStackTrace();
			return null;
		}
		ret.extra=loadMap("Extras",uid);
		ret.metadata=loadMap("Metadatas",uid);
		ret.children=loadUidList("Children","child",uid);
		ret.hashers=loadList("Hashers","hasher",uid);
		ret.finishedHashesEx=loadHashData("Hashes",uid);
		ret.targetHashesEx=loadHashData("TargetHashes",uid);
		ret.downconvertHashData();
		return ret;
	}

	/**
	 * Loads map data out of the given table that corresponds to the given UID
	 * @param table	What table to use
	 * @param uid	What UID's data to find
	 * @return		A map of data, or null if there was an error
	 */
	private Map<String,String> loadMap(String table, long uid)
	{
		Map<String,String> ret=new TreeMap<String,String>();
		Statement s;
		try {
			s = conn.createStatement();
		} catch (SQLException e) {
			log(Level.FATAL, "Library - Load Map ("+table+")", "Failed to connect to database");
			log(Level.DEBUG, "Library - Load Map ("+table+")", e.getMessage());
			e.printStackTrace();
			return null;
		}
		try {
			s.execute("SELECT key,value FROM "+table+" WHERE uid="+uid);
		} catch (SQLException e) {
			log(Level.FATAL, "Library - Load Map ("+table+")", "Failed to load map from the database due to improper sql");
			log(Level.DEBUG, "Library - Load Map ("+table+")", e.getMessage());
			e.printStackTrace();
			return null;
		}
		try {
			ResultSet set=s.getResultSet();
			while (set.next())
			{
				ret.put(set.getString(1), set.getString(2));
			}
			set.close();
			s.close();
			conn.commit();
		} catch (SQLException e) {
			log(Level.FATAL, "Library - Load Map ("+table+")", "Could not load map from the database, could not parse results");
			log(Level.DEBUG, "Library - Load Map ("+table+")", e.getMessage());
			e.printStackTrace();
			return null;
		}
		return ret;
	}
	/**
	 * Loads list data out of the given table that corresponds to the given UID
	 * @param table	What table to use
	 * @param column What column from the table to list
	 * @param uid	What UID's data to find
	 * @return		A list of data, or null if there was an error
	 */
	private List<String> loadList(String table, String column, long uid)
	{
		List<String> ret=new LinkedList<String>();
		Statement s;
		try {
			s = conn.createStatement();
		} catch (SQLException e) {
			log(Level.FATAL, "Library - Load List ("+table+")", "Failed to connect to database");
			log(Level.DEBUG, "Library - Load List ("+table+")", e.getMessage());
			e.printStackTrace();
			return null;
		}
		try {
			s.execute("SELECT "+column+" FROM "+table+" WHERE uid="+uid);
		} catch (SQLException e) {
			log(Level.FATAL, "Library - Load List ("+table+")", "Failed to load list from the database due to improper sql");
			log(Level.DEBUG, "Library - Load List ("+table+")", e.getMessage());
			e.printStackTrace();
			return null;
		}
		try {
			ResultSet set=s.getResultSet();
			while (set.next())
			{
				ret.add(set.getString(1));
			}
			set.close();
			s.close();
			conn.commit();
		} catch (SQLException e) {
			log(Level.FATAL, "Library - Load List ("+table+")", "Could not load list from the database, could not parse results");
			log(Level.DEBUG, "Library - Load List ("+table+")", e.getMessage());
			e.printStackTrace();
			return null;
		}
		return ret;
	}
	private List<Long> loadUidList(String table, String column, long uid)
	{
		List<Long> ret=new LinkedList<Long>();
		Statement s;
		try {
			s = conn.createStatement();
		} catch (SQLException e) {
			log(Level.FATAL, "Library - Load Uid List ("+table+")", "Failed to connect to database");
			log(Level.DEBUG, "Library - Load Uid List ("+table+")", e.getMessage());
			e.printStackTrace();
			return null;
		}
		try {
			s.execute("SELECT "+column+" FROM "+table+" WHERE uid="+uid+" ORDER BY index ASC");
		} catch (SQLException e) {
			log(Level.FATAL, "Library - Load Uid List ("+table+")", "Failed to load list from the database due to improper sql");
			log(Level.DEBUG, "Library - Load Uid List ("+table+")", e.getMessage());
			e.printStackTrace();
			return null;
		}
		try {
			ResultSet set=s.getResultSet();
			while (set.next())
			{
				ret.add(set.getLong(1));
			}
			set.close();
			s.close();
			conn.commit();
		} catch (SQLException e) {
			log(Level.FATAL, "Library - Load Uid List ("+table+")", "Could not load list from the database, could not parse results");
			log(Level.DEBUG, "Library - Load Uid List ("+table+")", e.getMessage());
			e.printStackTrace();
			return null;
		}
		return ret;
	}
	private List<Range> loadRangeList(String table, long uid)
	{
		List<Range> ret=new LinkedList<Range>();
		PreparedStatement s;
		try {
			s=conn.prepareStatement("SELECT offset, len FROM "+table+" WHERE uid=? ORDER BY offset");
			s.setLong(1, uid);
			s.execute();
		} catch (SQLException e) {
			log(Level.FATAL, "Library - Load Range List ("+table+")", "Failed to load list from the database due to improper sql");
			log(Level.DEBUG, "Library - Load Range List ("+table+")", e.getMessage());
			e.printStackTrace();
			return null;
		}
		try {
			ResultSet set=s.getResultSet();
			while (set.next())
			{
				Range temp = new Range(set.getLong(1),set.getLong(2));
				ret.add(temp);
			}
			set.close();
			s.close();
		} catch (SQLException e) {
			log(Level.FATAL, "Library - Load Range List ("+table+")", "Could not load list from the database, could not parse results");
			log(Level.DEBUG, "Library - Load Range List ("+table+")", e.getMessage());
			e.printStackTrace();
			return null;
		}
		return ret;
	}
	/**
	 * Loads map data out of the given table that corresponds to the given UID
	 * @param table	What table to use
	 * @param uid	What UID's data to find
	 * @return		A map of data, or null if there was an error
	 */
	private List<HashDataEx> loadHashData(String table, long uid)
	{
		List<HashDataEx> ret=new LinkedList<HashDataEx>();
		Statement s;
		try {
			s = conn.createStatement();
		} catch (SQLException e) {
			log(Level.FATAL, "Library - Load Hash ("+table+")", "Failed to connect to database");
			log(Level.DEBUG, "Library - Load Hash ("+table+")", e.getMessage());
			e.printStackTrace();
			return null;
		}
		try {
			s.execute("SELECT algorithm, offset, len, verified, downloaded, data FROM "+table+" WHERE uid="+uid);
		} catch (SQLException e) {
			log(Level.FATAL, "Library - Load Hash ("+table+")", "Failed to load hash from the database due to improper sql");
			log(Level.DEBUG, "Library - Load Hash ("+table+")", e.getMessage());
			e.printStackTrace();
			return null;
		}
		try {
			ResultSet set=s.getResultSet();
			while (set.next())
			{
				HashDataEx temp=new HashDataEx();
				temp.uid=uid;
				temp.algorithm=set.getString(1);
				temp.isVerified=set.getBoolean(4);
				temp.isDownloaded=set.getBoolean(5);
				temp.range=new Range();
				temp.range.start=set.getLong(2);
				temp.range.len=set.getLong(3);
				if (temp.range.start==-1 && temp.range.len==-1)
					temp.range=null;
				temp.hashdata=set.getString(6);
				ret.add(temp);
			}
			set.close();
			s.close();
			conn.commit();
		} catch (SQLException e) {
			log(Level.FATAL, "Library - Load Hash ("+table+")", "Could not load hash from the database, could not parse results");
			log(Level.DEBUG, "Library - Load Hash ("+table+")", e.getMessage());
			e.printStackTrace();
			return null;
		}
		return ret;
	}
	
	public void save(ItemInfo saved)
	{
		try {
			PreparedStatement s=conn.prepareStatement("UPDATE Objects SET name = ?, path = ?, size = ?, dir = ?, shared = ?, lastscan = ? WHERE uid=?");
			s.setString(1, saved.name);
			s.setString(2, saved.pathname);
			s.setLong(3, saved.size);
			s.setBoolean(4, saved.isDirectory);
			s.setBoolean(5, saved.isShared);
			if (saved.lastscan!=null)
				s.setTimestamp(6, new Timestamp(saved.lastscan.getTime()));
			else
				s.setTimestamp(6, null);
			s.setLong(7, saved.uid);
			s.execute();
			s.close();
			conn.commit();
		} catch (SQLException e) {
			log(Level.FATAL, "Library - Save", "Failed to load Shared Item from the database due to improper sql");
			log(Level.DEBUG, "Library - Save", e.getMessage());
			e.printStackTrace();
			return;
		}
		saveMap("Extras",saved.uid,saved.extra);
		saveMap("Metadatas",saved.uid,saved.metadata);
		saveUidList("Children","child",saved.uid,saved.children);
		saveList("Hashers","hasher",saved.uid,saved.hashers);
		saveHashData("Hashes",saved.uid,saved.finishedHashesEx);
		saveHashData("TargetHashes",saved.uid,saved.targetHashesEx);
	}
	
	private void saveMap(String table, long uid, Map<String,String> map)
	{
		if (map==null)
		{
			log(Level.DEBUG, "Library - Save Map", "Skipping because of null map");
			return;
		}
		// delete all old map values with this uid
		try {
			PreparedStatement s = conn.prepareStatement("DELETE FROM "+table+" WHERE uid=?");
			s.setLong(1, uid);
			s.execute();
			s.close();
		} catch (SQLException e) {
			log(Level.FATAL, "Library - Save Map", "Failed to delete old values");
			log(Level.DEBUG, "Library - Save Map", e.getMessage());
			e.printStackTrace();
			return;
		}

		// insert the keys
		try {
			PreparedStatement s=conn.prepareStatement("INSERT INTO "+table+" (uid, key, value) VALUES (?,?,?)");
			for (Entry<String, String> entry:map.entrySet())
			{
				s.setLong(1, uid);
				s.setString(2, entry.getKey());
				s.setString(3, entry.getValue());

				s.execute();
			}
			s.close();
			conn.commit();
		} catch (Exception e) {
			log(Level.FATAL, "Library - Save Map", "Failed to insert new map data");
			log(Level.DEBUG, "Library - Save Map", e.getMessage());
			e.printStackTrace();
			return;
		}
	}
	private void saveList(String table, String column, long uid, List<String> list)
	{
		if (list==null)
		{
			log(Level.DEBUG, "Library - Save List", "Skipping because of null list");
			return;
		}
		// delete all old list values with this uid
		try {
			PreparedStatement s = conn.prepareStatement("DELETE FROM "+table+" WHERE uid=?");
			s.setLong(1, uid);
			s.execute();
			s.close();
		} catch (SQLException e) {
			log(Level.FATAL, "Library - Save List", "Failed to delete old values");
			log(Level.DEBUG, "Library - Save List", e.getMessage());
			e.printStackTrace();
			return;
		}

		// insert the keys
		try {
			PreparedStatement s=conn.prepareStatement("INSERT INTO "+table+" (uid,"+column+") VALUES (?,?)");
			for (String key:list)
			{
				s.setLong(1, uid);
				s.setString(2, key);
				s.execute();
			}
			s.close();
			conn.commit();
		} catch (Exception e) {
			log(Level.FATAL, "Library - Save List", "Failed to insert new list data");
			log(Level.DEBUG, "Library - Save List", e.getMessage());
			e.printStackTrace();
			return;
		}
	}
	private void saveUidList(String table, String column, long uid, List<Long> list)
	{
		if (list==null)
		{
			log(Level.DEBUG, "Library - Save UID List", "Skipping because of null list");
			return;
		}
		// delete all old list values with this uid
		try {
			PreparedStatement s = conn.prepareStatement("DELETE FROM "+table+" WHERE uid=?");
			s.setLong(1, uid);
			s.execute();
			s.close();
		} catch (SQLException e) {
			log(Level.FATAL, "Library - Save UID List", "Failed to delete old values");
			log(Level.DEBUG, "Library - Save UID List", e.getMessage());
			e.printStackTrace();
			return;
		}

		// insert the keys
		try {
			PreparedStatement s=conn.prepareStatement("INSERT INTO "+table+" (uid,"+column+",index) VALUES (?,?,?)");
			for (int index=0; index<list.size(); index++)
			{
				long key=list.get(index);
				log(Level.VERBOSE,"Library - Save UID List", "Saving child: "+key);
				s.setLong(1, uid);
				s.setLong(2, key);
				s.setInt(3, index);
				s.execute();
			}
			s.close();
			conn.commit();
		} catch (Exception e) {
			log(Level.FATAL, "Library - Save UID List", "Failed to insert new list data");
			log(Level.DEBUG, "Library - Save UID List", e.getMessage());
			e.printStackTrace();
			return;
		}
	}
	private void saveRangeList(String table, long uid, List<Range> list)
	{
		if (list==null)
		{
			log(Level.DEBUG, "Library - Save Range List", "Skipping because of null list");
			return;
		}
		// delete all old list values with this uid
		try {
			PreparedStatement s = conn.prepareStatement("DELETE FROM "+table+" WHERE uid=?");
			s.setLong(1, uid);
			s.execute();
			s.close();
		} catch (SQLException e) {
			log(Level.FATAL, "Library - Save Range List", "Failed to delete old values");
			log(Level.DEBUG, "Library - Save Range List", e.getMessage());
			e.printStackTrace();
			return;
		}

		// insert the keys
		try {
			PreparedStatement s=conn.prepareStatement("INSERT INTO "+table+" (uid,offset,len) VALUES (?,?,?)");
			for (Range range:list)
			{
				s.setLong(1, uid);
				s.setLong(2, range.start);
				s.setLong(3, range.len);
				s.execute();
			}
			s.close();
			conn.commit();
		} catch (Exception e) {
			log(Level.FATAL, "Library - Save Range List", "Failed to insert new list data");
			log(Level.DEBUG, "Library - Save Range List", e.getMessage());
			e.printStackTrace();
			return;
		}
	}
	private void saveHashData(String table, long uid, List<HashDataEx> data)
	{
		// delete all old list values with this uid
		try {
			PreparedStatement s = conn.prepareStatement("DELETE FROM "+table+" WHERE uid=?");
			s.setLong(1, uid);
			s.execute();
			s.close();
		} catch (SQLException e) {
			log(Level.FATAL, "Library - Save HashData", "Failed to delete old values");
			log(Level.DEBUG, "Library - Save HashData", e.getMessage());
			e.printStackTrace();
			return;
		}

		// insert the keys
		try {
			PreparedStatement s=conn.prepareStatement("INSERT INTO "+table+" (uid,algorithm, offset, len, verified, downloaded, data) VALUES (?,?,?,?,?,?,?)");
			for (HashDataEx key:data)
			{
				s.setLong(1, uid);
				s.setString(2, key.algorithm);
				if (key.range!=null)
				{
					s.setLong(3, key.range.start);
					s.setLong(4, key.range.len);
				}
				else
				{
					s.setLong(3, -1);
					s.setLong(4, -1);
				}
				s.setBoolean(5, key.isVerified);
				s.setBoolean(6, key.isDownloaded);
				s.setString(7, key.hashdata);
				s.execute();
			}
			s.close();
			conn.commit();
		} catch (Exception e) {
			log(Level.FATAL, "Library - Save HashData", "Failed to insert new hash data");
			log(Level.DEBUG, "Library - Save HashData", e.getMessage());
			e.printStackTrace();
			return;
		}
	}
}
